package com.fare.taluo.queue;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.annotation.Resource;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Border;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.lang3.StringUtils;

import com.dayatang.domain.Entity;
import com.dayatang.domain.InstanceFactory;
import com.fare.taluo.application.report.csair.ZhairReportApplication;
import com.fare.taluo.application.system.email.EmailSendApplication;
import com.fare.taluo.application.system.email.ReportEmailApplication;
import com.fare.taluo.core.domain.BaseEntity;
import com.fare.taluo.core.domain.queue.message.QueueEmailMessageEntity;
import com.fare.taluo.util.DateUtils;
import com.fare.taluo.vo.report.ZhairReportEntityVO;
import com.fare.taluo.vo.reportemail.EmailVO;
import com.fare.util.ReportProperties;

public class ExportZHairReportQuartzJob extends BaseEntity<ExportZHairReportQuartzJob> implements Entity{
	/**
	 * 
	 */
	private static final long serialVersionUID = -5465814448073309786L;
	private String email = null;
	private String currentDate = DateUtils.getDateStr("yyyy-MM-dd");
	private List<String> dateList = getDateList();
	@Resource(name="zhairReportApplication")
	private ZhairReportApplication zhairReportApplication;
	private jxl.write.WritableFont titleFont;
	private jxl.write.WritableCellFormat titleFormat;
	private jxl.write.WritableFont normalFont;
	private jxl.write.WritableFont boldFont;
	private jxl.write.WritableCellFormat normalFormat;
	private jxl.write.WritableCellFormat boldFormat;
	private jxl.write.WritableCellFormat redFormat;
	private static final int COLUMN_WIDTH = 13;
	@Resource(name ="reportEmailApplication")
	private ReportEmailApplication reportEmailApplication;
	
	private List<EmailVO> emailList;
	private List<Map<String,Map<String,Map<String,ZhairReportEntityVO>>>> finalList = new ArrayList<Map<String,Map<String,Map<String,ZhairReportEntityVO>>>>();
	public void execute() throws Exception {
		try {
		
			//获取后台要发送的Email
			if(email==null || email==""){
				emailList=reportEmailApplication.findAllEmail(1);//0表示不接受状态，1表示接受状态
				StringBuffer emailStr=new StringBuffer();
				for(EmailVO emailVo:emailList){
					emailStr.append(","+emailVo.getEmailAddress());
				}
				if(emailStr.length()>1 && emailStr !=null){
					email=emailStr.toString().substring(1);
				}
			}
			
			// 获得数据
			for (String dateString  : dateList) {
				finalList.add(this.getReportMap(dateString));	
			}
			
			//生成路径
			String path = ReportProperties.getInstance().getSavePath().endsWith("/")
					? ReportProperties.getInstance().getSavePath() :
						ReportProperties.getInstance().getSavePath()+"/";
			
			String fileName = "深航官网对比渠道价差对比报表_"+currentDate+".xls";
			//生成excel
			exportExcel(finalList,path,fileName);
		
			File file = new File(path+fileName);
			if(file.exists() && StringUtils.isNotBlank(getEmail())){
				QueueEmailMessageEntity email = new QueueEmailMessageEntity();
				email.setAttachPath(file.getPath());//new String(location.getBytes("UTF-8"),"ISO8859-1")
				email.setBody("<p>您好!<p><p>&nbsp;&nbsp;已生成您需要的深航官网对比渠道价差对比报表，"+"</p>"
				+"<p>请查收附件。</p>");
				email.setSubject("深航官网对比渠道价差对比报表"+currentDate);
				email.setToAddress(getEmail());
				email.setName("深航官网对比渠道价差对比报表");
				InstanceFactory.getInstance(EmailSendApplication.class).save(email);
			
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		
		
	}
	
	private void exportExcel(List<Map<String,Map<String,Map<String,ZhairReportEntityVO>>>> finalList,String path,String fileName) throws Exception{
		WritableWorkbook wwb = null;
		OutputStream os = null;
		File file = null;
		try{
			file = this.createFile(path, fileName);
			os=new FileOutputStream(file);
			wwb = Workbook.createWorkbook(os); // 建立excel文件
			// 定义样式
			jxl.write.WritableFont normalFont = new jxl.write.WritableFont(
					WritableFont.createFont("宋体"), 10);
			jxl.write.WritableCellFormat normalFormat = new jxl.write.WritableCellFormat(
					normalFont);
			normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
			normalFormat.setAlignment(Alignment.CENTRE);
			normalFormat.setWrap(false);
			for (int i = 0; i < dateList.size(); i++) {
				WritableSheet ws = wwb.createSheet(dateList.get(i), i); // sheet名称
				ws = setHeadFirst(ws);
				ws = setDataFirst(ws,finalList.get(i));
			}
			wwb.write();
		}finally{
			
			if(wwb != null) wwb.close();
			if(os != null) os.close();
			wwb = null;
			os = null;
			file = null;
		}		
	}
	/**
	 * 设置每个sheet
	 * @param ws
	 * @param infoList   航线-航班号-渠道集
	 * @return
	 * @throws Exception
	 */
	private WritableSheet setDataFirst(WritableSheet ws,Map<String,Map<String,Map<String,ZhairReportEntityVO>>> infoMap) throws Exception {
		this.formatWriteable();
		//把同样航线的放到一个Map中
		TreeMap<String,Map<String,Map<String,ZhairReportEntityVO>>> treeMap1 = new TreeMap<String,Map<String,Map<String,ZhairReportEntityVO>>>(infoMap);
		Iterator lineKeys = treeMap1.keySet().iterator();
		int i=1;
		int col = 1;
		int colTemp = 0;
		while(lineKeys.hasNext()){
			List<ZhairReportEntityVO> compareList = new ArrayList<ZhairReportEntityVO>();
			String key =(String) lineKeys.next();//航线
			Map<String,Map<String,ZhairReportEntityVO>> map = infoMap.get(key);
			Iterator numKeys = map.keySet().iterator();//航班号
			while(numKeys.hasNext()){
				String nkey =(String) numKeys.next();
				ZhairReportEntityVO  zh_qu = map.get(nkey).get("20000-00002");
				ZhairReportEntityVO  zh_xie = map.get(nkey).get("20000-00003");
				ZhairReportEntityVO  zh_yi = map.get(nkey).get("20000-00004");
				ZhairReportEntityVO  cs_qu = map.get(nkey).get("20005-00002");
				ZhairReportEntityVO  cs_xie = map.get(nkey).get("20005-00003");
				ZhairReportEntityVO  cs_yi = map.get(nkey).get("20005-00004");
				if(nkey.indexOf("ZH")!=-1){
					if(zh_qu!=null){
						compareList.add(zh_qu);
					}else if(zh_xie!=null){
						compareList.add(zh_xie);
					}else if(zh_yi!=null){
						compareList.add(zh_yi);
					}
					
				}else{
					if(cs_qu!=null){
						compareList.add(cs_qu);
					}else if(cs_xie!=null){
						compareList.add(cs_xie);
					}else if(cs_yi!=null){
						compareList.add(cs_yi);
					}
					
				}
			}
			ComparatorReport comparator = new ComparatorReport();
			Collections.sort(compareList, comparator);
			
			// 循环航班号
			for (ZhairReportEntityVO zhairReportEntityVO : compareList) {
				String temp  =zhairReportEntityVO.getHbaseKey() + "-" + zhairReportEntityVO.getChannelKey();
				Map<String,ZhairReportEntityVO> map1 =  map.get(zhairReportEntityVO.getFlightNo());
				ZhairReportEntityVO qu = null;
				ZhairReportEntityVO xie = null;
				ZhairReportEntityVO yi = null;
				WritableCellFormat tempFormat = null;
				if(zhairReportEntityVO.getFlightNo().indexOf("ZH") != -1){
					 qu  = map1.get("20000-00002");
					 xie  = map1.get("20000-00003");
					 yi  = map1.get("20000-00004");
					 tempFormat = normalFormat;
				}else{
					qu  = map1.get("20005-00002");
					xie  = map1.get("20005-00003");
					yi  = map1.get("20005-00004");
					tempFormat = redFormat;
				}
				
				//航线
				Label label0 = new Label(0, i ,key, normalFormat);
				ws.addCell(label0);
				//航空公司
				Label label1 = new Label(1, i ,zhairReportEntityVO.getFlightNo().indexOf("ZH")!=-1?"深航":"南航", tempFormat);
				ws.addCell(label1);
				//航班
				Label label2 = new Label(2, i ,zhairReportEntityVO.getFlightNo(), tempFormat);
				ws.addCell(label2);
				//航班时间
				Label label3 = new Label(3, i ,zhairReportEntityVO.getFlightTime(), tempFormat);
				ws.addCell(label3);
				  
				   if(null != qu){
					   Label label4 = new Label(4, i ,qu.getPrice()+"", tempFormat);
						ws.addCell(label4);
					
						Label label5 = new Label(5, i ,qu.getChannelPrice()+"", tempFormat);
						ws.addCell(label5);
						
						Label label6 = new Label(6, i ,qu.getSpreadAmount()+"", tempFormat);
						ws.addCell(label6);
						
						Label label7 = new Label(7, i ,String.format("%.2f",qu.getSpread()*100)+"%", tempFormat);
						ws.addCell(label7);
				   }else{
					   Label label4 = new Label(4, i ,"", tempFormat);
						ws.addCell(label4);
					
						Label label5 = new Label(5, i ,"", tempFormat);
						ws.addCell(label5);
						
						Label label6 = new Label(6, i ,"", tempFormat);
						ws.addCell(label6);
						
						Label label7 = new Label(7, i ,"", tempFormat);
						ws.addCell(label7);
				   }
					
				   if(null!= xie){
					
						Label label8 = new Label(8, i ,xie.getChannelPrice()+"", tempFormat);
						ws.addCell(label8);
						
						Label label9 = new Label(9, i ,xie.getSpreadAmount()+"", tempFormat);
						ws.addCell(label9);
						
						Label label10 = new Label(10, i ,String.format("%.2f",xie.getSpread()*100)+"%", tempFormat);
						ws.addCell(label10);
				   }else{
					   Label label8 = new Label(8, i ,"", tempFormat);
						ws.addCell(label8);
						
						Label label9 = new Label(9, i ,"", tempFormat);
						ws.addCell(label9);
						
						Label label10 = new Label(10, i ,"", tempFormat);
						ws.addCell(label10);
				   }
				
				   if(null!= yi){
						
						Label label11 = new Label(11, i ,yi.getChannelPrice()+"", tempFormat);
						ws.addCell(label11);
						
						Label label12 = new Label(12, i ,yi.getSpreadAmount()+"", tempFormat);
						ws.addCell(label12);
						
						Label label13 = new Label(13, i ,String.format("%.2f",yi.getSpread()*100)+"%", tempFormat);
						ws.addCell(label13);
				   }else{
					   Label label11 = new Label(11, i ,"", tempFormat);
						ws.addCell(label11);
						
						Label label12 = new Label(12, i ,"", tempFormat);
						ws.addCell(label12);
						
						Label label13 = new Label(13, i ,"", tempFormat);
						ws.addCell(label13);
				   }
				++i;
			}
			if (col < i) {
				ws.mergeCells(0, col, 0, i - 1);
				col = i;
			}
		}
		return ws;
	}
	private void formatWriteable() throws WriteException {
		titleFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				10, WritableFont.BOLD);
		titleFont.setColour(Colour.WHITE);
		titleFormat = new jxl.write.WritableCellFormat(titleFont);
		titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
		titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
		titleFormat.setAlignment(Alignment.CENTRE); // 水平对齐
		titleFormat.setWrap(true); // 是否换行
		titleFormat.setBackground(Colour.OCEAN_BLUE);// 背景色暗灰-25%
		
		
		
		normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11);
		normalFormat = new jxl.write.WritableCellFormat(normalFont);
		normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		normalFormat.setAlignment(Alignment.CENTRE);
		normalFormat.setWrap(false);
		
		
		normalFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11);
		
		boldFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
				11,WritableFont.BOLD);
		boldFormat = new jxl.write.WritableCellFormat(boldFont);
		boldFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		boldFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		boldFormat.setAlignment(Alignment.CENTRE);
		boldFormat.setWrap(false);
		
		redFormat = new jxl.write.WritableCellFormat(normalFont);
		redFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		redFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		redFormat.setAlignment(Alignment.CENTRE);
		redFormat.setWrap(false);
		redFormat.setBackground(Colour.PINK);
	}
	private WritableSheet setHeadFirst(WritableSheet ws) throws Exception {
		this.formatWriteable();
		
		Label label0 = new Label(0, 0, "航线", titleFormat);
		ws.addCell(label0);
		ws.setColumnView(0, COLUMN_WIDTH);
		Label label1 = new Label(1, 0, "航空公司", titleFormat);
		ws.addCell(label1);
		ws.setColumnView(1, COLUMN_WIDTH);
		Label label2 = new Label(2, 0, "航班", titleFormat);
		ws.addCell(label2);
		ws.setColumnView(2, COLUMN_WIDTH);
		Label label3 = new Label(3, 0, "航班时间", titleFormat);
		ws.addCell(label3);
		ws.setColumnView(3, COLUMN_WIDTH);
		Label label4 = new Label(4, 0, "官网", titleFormat);
		ws.addCell(label4);
		ws.setColumnView(4, COLUMN_WIDTH);
		Label label5 = new Label(5, 0, "去哪儿", titleFormat);
		ws.addCell(label5);
		ws.setColumnView(5, COLUMN_WIDTH);
		Label label6 = new Label(6, 0, "价差", titleFormat);
		ws.addCell(label6);
		ws.setColumnView(6, COLUMN_WIDTH);
		Label label7 = new Label(7, 0, "价差比", titleFormat);
		ws.addCell(label7);
		ws.setColumnView(7, COLUMN_WIDTH);
		Label label8 = new Label(8, 0, "携程", titleFormat);
		ws.addCell(label8);
		ws.setColumnView(8, COLUMN_WIDTH);
		Label label9 = new Label(9, 0, "价差", titleFormat);
		ws.addCell(label9);
		ws.setColumnView(9, COLUMN_WIDTH);
		Label label10 = new Label(10, 0, "价差比", titleFormat);
		ws.addCell(label10);
		ws.setColumnView(10, COLUMN_WIDTH);
		Label label11 = new Label(11, 0, "艺龙", titleFormat);
		ws.addCell(label11);
		ws.setColumnView(11, COLUMN_WIDTH);
		Label label12 = new Label(12, 0, "价差", titleFormat);
		ws.addCell(label12);
		ws.setColumnView(12, COLUMN_WIDTH);
		Label label13 = new Label(13, 0, "价差比", titleFormat);
		ws.addCell(label13);
		ws.setColumnView(13, COLUMN_WIDTH);
		return ws;
	}
	
	/**
	 * 获得一个sheet的数据
	 * @param dateStr 日期
	 * @return
	 */ //    
	private Map<String,Map<String,Map<String,ZhairReportEntityVO>>> getReportMap(String dateStr){    
		
		Map<String,Map<String,Map<String,ZhairReportEntityVO>>> resultMap = new HashMap<String,Map<String,Map<String,ZhairReportEntityVO>>>();
			
			List<String> flightInfo = getFilghtInfo();
			//每条航线
			for (String string : flightInfo) {
				String fromCityCode = string.split("-")[0];
				String toCityCode = string.split("-")[1];
				//根据日期获得该日期下得航班信息,该航线
				// <zhairReportEntityVO.getFlightNo()<hbasekey, >>
				Map<String,Map<String,ZhairReportEntityVO>> flightMap = new HashMap<String, Map<String,ZhairReportEntityVO>>();
				List<ZhairReportEntityVO> infoList = zhairReportApplication.findZhairReportEntityByCondition(null, fromCityCode, toCityCode, dateStr);
				for (ZhairReportEntityVO zhairReportEntityVO : infoList) {
					String key = zhairReportEntityVO.getFlightNo();
					String flg =zhairReportEntityVO.getHbaseKey()+"-"+zhairReportEntityVO.getChannelKey();
					//把航班号相同的放在一起
					if(flightMap.containsKey(key)){
						Map<String,ZhairReportEntityVO> list = flightMap.get(key);
						
						if(!list.containsKey(flg)){
							list.put(flg,zhairReportEntityVO);
						}
						flightMap.put(key, list);
					}else{
						Map<String,ZhairReportEntityVO> list = new HashMap<String,ZhairReportEntityVO>();
						if(!list.containsKey(flg)){
							list.put(flg,zhairReportEntityVO);
						}
						flightMap.put(key, list);
					}
				}
				resultMap.put(fromCityCode+"-"+toCityCode, flightMap);	
			}
		return resultMap;
	}
	
	/**
	 * +3 +7 +15
	 * @return
	 */
	private List<String> getDateList(){
		List<String> dateList = new ArrayList<String>();	
			dateList.add(DateUtils.getDateAfter(3));		
			dateList.add(DateUtils.getDateAfter(7));		
			dateList.add(DateUtils.getDateAfter(15));		

		return dateList;
	}
	@Override
	public Serializable getId() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isNew() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean existed() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean notExisted() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean existed(String propertyName, Object propertyValue) {
		// TODO Auto-generated method stub
		return false;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}
	
	public List<EmailVO> getEmailList() {
		return emailList;
	}

	public void setEmailList(List<EmailVO> emailList) {
		this.emailList = emailList;
	}

	public String getCurrentDate() {
		return currentDate;
	}

	public void setCurrentDate(String currentDate) {
		this.currentDate = currentDate;
	}
	public List<String> getFilghtInfo(){
		List<String> list = new ArrayList<String>();
		list.add("SZX-KHN");
		list.add("HAK-CAN");
		list.add("SZX-SYX");
		list.add("SZX-KWL");
		list.add("CAN-HAK");
		list.add("KWE-CAN");
		list.add("FOC-SZX");
		list.add("HAK-SZX");
		list.add("SZX-HAK");
		list.add("DLC-KWE");//DLC-CGO-KWE
		list.add("CAN-NNG");
		list.add("PVG-SZX");
		list.add("KWL-NKG");
		list.add("SZX-KWE");
		list.add("PEK-NNG");
		list.add("CKG-SZX");
		list.add("NNG-CAN");
		list.add("SZX-PEK");
		list.add("CKG-NKG");
		list.add("PEK-SZX");
		list.add("KWE-SZX");
		list.add("NNG-PEK");
		list.add("SZX-NNG");
		list.add("SHA-SZX");
		list.add("CAN-KWE");
		list.add("CAN-WUX");
		list.add("CAN-WNZ");
		list.add("CAN-NKG");
		list.add("HGH-SZX");
		list.add("SZX-INC");//SZX-XIY-INC
		list.add("WNZ-CAN");
		list.add("CAN-CTU");
		list.add("SZX-CGO");
		list.add("CTU-SZX");
		return list;
	}
	private File createFile(String filePath, String fileName)
			throws IOException {
		String excelpath = filePath;
		System.out.println("输出路径：" + excelpath);
		// 创建文件
		File path = new File(excelpath);
		if (path.exists()) {
			path.delete();
			path.createNewFile();
		} else {
			path.mkdirs();
		}
		File file = new File(filePath + File.separator + fileName);
		file.createNewFile();
		return file;

	}
}
class ComparatorReport implements Comparator{

	 public int compare(Object arg0, Object arg1) {
		 ZhairReportEntityVO user0=(ZhairReportEntityVO)arg0;
		 ZhairReportEntityVO user1=(ZhairReportEntityVO)arg1;
		/* int flag = user0.getHbaseKey().compareTo(user1.getHbaseKey());
		 if(flag==0){*/
			 return user0.getFlightTime().compareTo(user1.getFlightTime());
		/* }else{
			 return flag;
		 }*/
		 
		 
	   //首先比较年龄，如果年龄相同，则比较名字
		 
	 }
	 
	}
